// ================================================================================
// 		File: tbPartyArticles.cs
// 		Desc: 
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（）
// 		Date: 2015-12-31 00:08
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
// 
// ================================================================================

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Text;
using ZHDJ.Core;

namespace ZHDJ.Data.Models.Common
{
    /// <summary>
    /// 文章
    /// </summary>   
    public class PartyArticle : EntityBaseOperation
    {
        #region 属性


        /// <summary>
        /// 文章标题
        /// </summary>
        public string Title { get; set; }

        /// <summary>
        /// 文章副标题
        /// </summary>
        public string Subhead { get; set; }

        /// <summary>
        /// 文章标签
        /// </summary>
        public string ArticlesLable { get; set; }

        /// <summary>
        /// 文章内容
        /// </summary>
        public string Content { get; set; }

        /// <summary>
        /// 文章简介
        /// </summary>
        public string Summary { get; set; }

        /// <summary>
        /// 所属组织
        /// </summary>
        public string BranchID { get; set; }

        /// <summary>
        /// 文章所属模块
        /// </summary>
        public string Module { get; set; }

        /// <summary>
        /// 文章类型
        /// </summary>
        public string Category { get; set; }

        /// <summary>
        /// 来源Id（以后用于推送）
        /// </summary>
        public string SourceId { get; set; }

        /// <summary>
        /// 作者
        /// </summary>
        public string Author { get; set; }

        /// <summary>
        /// 原链接
        /// </summary>
        public string AuthorUrl { get; set; }

        /// <summary>
        /// 1 草稿  4发布 
        /// </summary>
        public int Status { get; set; }

        /// <summary>
        /// 1审核中 2驳回  4审核通过
        /// </summary>
        public int? Aduits { get; set; }

        /// <summary>
        /// （主）图片
        /// </summary>
        public string ImageID { get; set; }

        /// <summary>
        /// 0、无；1、置顶；2、固顶；3、置顶+固顶；
        /// </summary>
        public int Flags { get; set; }

        /// <summary>
        /// 活动开始时间
        /// </summary>
        public DateTime? ActivityBeginTime { get; set; }

        /// <summary>
        /// 活动结束时间
        /// </summary>
        public DateTime? ActivityEndTime { get; set; }

        /// <summary>
        /// 浏览数
        /// </summary>
        public int Views { get; set; }

        public virtual ICollection<PartyArticlesFile> PartyArticlesFiles { get; set; }
        #endregion

        public static DataSet GetPartyArticleGridData(int pageIndex, int pageSize, string module, string category, string searchKey, string banchId)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };
            StringBuilder sb = new StringBuilder();


            sb.Append(@"select SQL_CALC_FOUND_ROWS 
ID, Title, ou.BranchName, ArticlesLable, Author,Content,
article.Flags as Flags,
CASE article.Flags WHEN 0 THEN '无' WHEN 1 THEN '置顶' WHEN 2 THEN '固顶' WHEN 3 THEN '置顶+固顶' END AS FlagsEx,
case article.Category WHEN NULL then article.Module WHEN '' THEN article.Module else article.Category END as Category , 
article.`Status`,
CASE article.`Status` WHEN 1 THEN '草稿'  WHEN 4 THEN '发布' END as StatusEx,
article.Module
from tblpartyarticle article
LEFT JOIN  tblpartyorgbranch ou on article.BranchID = ou.BranchID
where 1 = 1");


            if (!string.IsNullOrEmpty(module))
            {
                sb.Append(" and Module=?Module");
                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = module
                });
            }
            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and Category=?Category");
                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Category",
                    Value = category
                });
            }
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and article.Title like ?Title ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Title",
                        Value = string.Format("%{0}%", searchKey)
                    });
            }
            if (!string.IsNullOrEmpty(banchId))
            {
                sb.Append(" and article.BranchID in (select childbranchid from tblpartyorgbranchrelation t where t.BranchID=?BranchID)");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "BranchID",
                        Value = banchId
                    });
            }

            sb.Append(" order by article.Status desc, article.CreateTime desc,article.Status desc limit ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            ZHDJ.Core.Log.Log4netHelper.WriteLog(sb.ToString() + module + category);
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 获取文章模块或类型
        /// </summary>
        /// <param name="module"></param>
        /// <param name="articleType"></param>
        /// <returns></returns>
        public static DataTable GetCategory(string module, string articleType = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select DDicMember,DDicMemberName from tbldatadictionary where DDicType=?DDicType");

            if (!string.IsNullOrEmpty(articleType))
            {
                sb.Append(" and DDicMember=?DDicMember");
            }

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("?DDicType",MySqlDbType.VarChar),
                new MySqlParameter("?DDicMember",MySqlDbType.VarChar)
            };
            parameters[0].Value = module;
            parameters[1].Value = articleType;

            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters);
        }

        /// <summary>
        /// 获取文章模块或类型
        /// </summary>
        /// <param name="module"></param>
        /// <param name="articleType"></param>
        /// <returns></returns>
        public static DataTable GetArticleSingle(string id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select a.*,d.DDicMemberName as CategoryName,b.BranchName from tblpartyarticle a 
left join tbldatadictionary d on (a.Module=d.DDicType and a.Category=d.DDicMember)
left join tblpartyorgbranch b on (b.branchid=a.branchid) where a.id=?id ");


            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("?id",MySqlDbType.VarChar),

            };
            parameters[0].Value = id;

            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters);
        }

        /// <summary>
        /// 根据模块（或类型）获取文章标题信息
        /// </summary>
        /// <param name="module"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public static DataSet GetArticleTitle(string branchId, string module, string category = "", string countFilter = "", bool getCreateTime = false)
        {
            StringBuilder sb = new StringBuilder();
            if (getCreateTime)
                sb.Append("select SQL_CALC_FOUND_ROWS ID,Title,CreateTime from tblpartyarticle where 1=1");
            else
                sb.Append("select SQL_CALC_FOUND_ROWS ID,Title from tblpartyarticle where 1=1");

            if (!string.IsNullOrEmpty(module))
            {
                sb.Append(" and Module=?Module");
            }

            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and BranchID in (select childbranchid from tblpartyorgbranchrelation where BranchID=?BranchID)");
            }

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and Category=?Category");
            }
            sb.Append(" and Status=4");  //已发布
            sb.AppendFormat(" order by CreateTime desc {0};SELECT FOUND_ROWS();", countFilter);

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("?Module",MySqlDbType.VarChar),
                new MySqlParameter("?Category",MySqlDbType.VarChar),
                new MySqlParameter("?BranchID",MySqlDbType.VarChar)
            };
            parameters[0].Value = module;
            parameters[1].Value = category;
            parameters[2].Value = branchId;

            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters);
        }

        public static DataSet GetMoreArticleTitle(int pageSize, int pageIndex, string branchId, string module, string category, bool isTreasureBox, string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            StringBuilder sb = new StringBuilder();

            if (isTreasureBox)
            {
                sb.Append(@"select SQL_CALC_FOUND_ROWS article.ID,article.Title,article.CreateTime,Url from tblpartyarticle article 
left join tblpartyfile file on article.ImageID=file.ID
where 1=1");
            }
            else
            {
                sb.Append("select SQL_CALC_FOUND_ROWS ID,Title,AuthorUrl,CreateTime from tblpartyarticle article where 1=1");
            }

            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and BranchID in (select childbranchid from tblpartyorgbranchrelation where BranchID=?BranchID)");
                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "BranchID",
                    Value = branchId
                });
            }

            if (!string.IsNullOrEmpty(module))
            {
                sb.Append(" and Module=?Module");
                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = module
                });
            }

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and Category=?Category");
                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Category",
                    Value = category
                });
            }

            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and article.Title like @Title ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Title",
                        Value = string.Format("%{0}%", searchKey)
                    });
            }

            sb.Append(" and Status=4 order by Flags,article.CreateTime desc ");  //已发布

            if (pageIndex > -1)
            {
                sb.AppendFormat(" LIMIT {0} ,{1}; SELECT FOUND_ROWS(); ", pageIndex, pageSize);
            }

            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 获取门户文章轮播图片
        /// </summary>
        /// <returns></returns>
        public static DataTable GetPortalArticleImage()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * from tblpartyarticle WHERE Module='Carousel' AND `Status`=4 AND (ImageID<>NULL or ImageID <>'') ORDER BY Flags DESC,CreateTime DESC");

            return MySqlHelper.ExecuteDataTable(sb.ToString());
        }

        #region 学习资料
        public static DataSet GetEduMateriaArticle(int pageIndex, int pageSize, string category, string articlesLable = null, string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = SysDataDictionary.EduMaterialKey
                },
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Category",
                    Value = category
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
                ,
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "ThemeCategory",
                    Value = SysDataDictionary.EduThemeCategory
                }
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.*,d.DDicMemberName as ThemeCategoryName,b.BranchName 
,
if(a.Status=4,'已发布','草稿') as StatusName
from tblpartyarticle a
left join tbldatadictionary d on (d.DDicType=?ThemeCategory and a.articlesLable=d.DDicMember)
left join tblpartyorgbranch b on (b.branchid=a.branchid)
where Module=?Module and Category=?Category ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and Title like @key ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "key",
                        Value = string.Format("%{0}%", searchKey)
                    });
            }
            if (!string.IsNullOrEmpty(articlesLable))
            {
                sb.Append(" and articlesLable=?articlesLable ");
                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "articlesLable",
                    Value = articlesLable
                });
            }
            sb.Append(" order by Status desc, Flags desc, CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }
        #endregion

        #region 网上学院 先进典型
        public static DataSet GetEduHonourArticle(int pageIndex, int pageSize, string category, string status, string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "Module",Value = SysDataDictionary.EduHonour},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.*,d.DDicMemberName as CategoryName,
                        b.BranchName,
                        if(a.Status=4,'已发布','草稿') as StatusName
                        from tblpartyarticle a
                        left join tbldatadictionary d on (d.DDicType=?Module and a.Category=d.DDicMember)
                        left join tblpartyorgbranch b on (b.branchid=a.branchid)
                        where Module=?Module ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and Title like ?key ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "key",
                        Value = string.Format("%{0}%", searchKey)
                    });
            }
            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and Category=?Category");
                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Category",
                    Value = category
                });
            }
            if (!string.IsNullOrEmpty(status))  //发布状态
            {
                sb.Append(" and a.Status=?Status ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Status", Value = status });
            }
            sb.Append(" order by Status desc, Flags desc, CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataSet GetEduDownloadLibrary(int pageIndex, int pageSize, string searchKey)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "Module",Value = "PartySchoolOnline"},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "Category",Value = "DownloadLibrary"},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS Id,Title,AuthorUrl,CreateTime
                        from tblpartyarticle
                        where Module=?Module and Category=?Category");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and Title like ?Title ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Title",
                        Value = string.Format("%{0}%", searchKey)
                    });
            }

            sb.Append(" order by CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }
        #endregion

        #region 群众路线 先进典型
        public static DataSet GetMassHonourArticle(int pageIndex, int pageSize, string status, string searchKey = "", string branchId = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "Module",Value = "MassLine"},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "Category",Value = "MassHonour"},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.*,
                        b.BranchName,
                        if(a.Status=4,'已发布','草稿') as StatusName
                        from tblpartyarticle a
                        left join tblpartyorgbranch b on (b.branchid=a.branchid)
                        where Module=?Module and Category=?Category ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and a.Title like ?key ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "key", Value = string.Format("%{0}%", searchKey) });
            }
            if (!string.IsNullOrEmpty(status))  //发布状态
            {
                sb.Append(" and a.Status=?Status ");
                parameters.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "Status", Value = status });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and a.branchId=?branchId ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "branchId", Value = branchId });
            }
            sb.Append(" order by Status desc, Flags desc, CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataTable GetBranchPortalMassHonourData(string branchId)
        {
            StringBuilder sb = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            sb.AppendFormat(@"select ID,Title from tblpartyarticle where Module='MassLine' and Category='MassHonour' 
            and  `Status`=4 ");
            if (!string.IsNullOrEmpty(branchId))  //发布状态
            {
                sb.Append(" and branchId=?branchId ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "branchId", Value = branchId });
            }
            sb.Append("order by CreateTime desc");

            return MySqlHelper.ExecuteDataTable(sb.ToString());
        }
        #endregion

        #region 心得体会
        public static DataSet GetPartySoundArticle(int pageIndex, int pageSize, string branchId, string createBy, string module, string searchKey, string currentLoginID = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = module
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.ID,a.Title,BranchName,UserName,a.CreateTime,
if(a.Status=4,'已发布','草稿') as StatusName
from tblpartyarticle a
left join tblpartyorgbranch b on b.branchid=a.branchid
left join tblpartyorgmember m on a.CreateBy=m.LoginID
where Module=?Module");

            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and a.Title like ?key");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "key",
                        Value = string.Format("%{0}%", searchKey)
                    });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and a.BranchID in (select ChildBranchID from tblpartyorgbranchrelation brr where brr.BranchID=?BranchID)");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "BranchID",
                        Value = string.Format("{0}", branchId)
                    });
            }
            if (!string.IsNullOrEmpty(createBy))
            {
                sb.Append(" and (UserName like ?Name or a.CreateBy like ?Name)");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Name",
                        Value = string.Format("%{0}%", createBy)
                    });
            }
            if (!string.IsNullOrEmpty(currentLoginID))
            {
                sb.AppendFormat(" and a.createBy='{0}'", currentLoginID);
            }
            sb.Append(" order by Status desc, Flags desc, CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }
        #endregion

        #region 关爱信息速递
        public static DataSet GetCareInfoCourierArticle(int pageIndex, int pageSize, string module, string category, string branchId, string searchKey)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = module
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Category",
                    Value = category
                }
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.ID,a.Title,ActivityBeginTime as ActivityTime,UserName,a.CreateTime,
if(a.Status=4,'是','否') as StatusName
from tblpartyarticle a
left join tblpartyorgmember m on a.CreateBy=m.LoginID
where Module=?Module and Category=?Category");

            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and Title like ?key ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "key",
                        Value = string.Format("%{0}%", searchKey)
                    });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and a.BranchID in (select ChildBranchID from tblpartyorgbranchrelation brr where brr.BranchID=?BranchID)");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "BranchID",
                        Value = string.Format("{0}", branchId)
                    });
            }

            sb.Append(" order by Status desc, Flags desc, CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }
        #endregion

        #region 党内招聘
        public static DataSet GetPartyRecruitData(int pageIndex, int pageSize, string searchKey, string beginDate, string endDate)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = "partylove"
                },
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Category",
                    Value = "PartyRecruit"
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.ID,a.Title,UserName,a.CreateTime,BranchName
from tblpartyarticle a
left join tblpartyorgmember m on a.CreateBy=m.LoginID
left join tblpartyorgbranch b on a.BranchID=b.BranchID
where Module=?Module and Category=?Category");

            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and Title like ?key ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "key",
                        Value = string.Format("%{0}%", searchKey)
                    });
            }
            if (!string.IsNullOrEmpty(beginDate))
            {
                sb.Append(" and DATE_FORMAT(a.CreateTime,'%Y-%m-%d')>=@BeginDate");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "BeginDate",
                        Value = string.Format("{0}", beginDate)
                    });
            }
            if (!string.IsNullOrEmpty(endDate))
            {
                sb.Append(" and DATE_FORMAT(a.CreateTime,'%Y-%m-%d')<=@EndDate");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "EndDate",
                        Value = string.Format("{0}", endDate)
                    });
            }

            sb.Append(" order by Status desc, Flags desc, CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataTable GetFileData(string articleId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select articlefile.ID,Title from tblpartyarticlesfile articlefile
left join tblpartyfile file on articlefile.FileID=file.ID
where ArticleID=?ArticleID order by articlefile.CreateTime desc");

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("?ArticleID",MySqlDbType.VarChar)
            };
            parameters[0].Value = articleId;

            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters);
        }
        #endregion

        #region 组织中心信息公开
        public static DataTable GetVillagePublicData(string branchId)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"select ID,Title from tblpartyarticle where Module='BranchHomeArticle' and Category='VillagePublic'
and BranchID='{0}' and `Status`=4
order by CreateTime desc limit 0,5", branchId);

            return MySqlHelper.ExecuteDataTable(sb.ToString());
        }

        /// <summary>
        /// 财务公开
        /// </summary>
        /// <param name="branchId"></param>
        /// <returns></returns>
        public static DataTable GetFinancePublicData(string branchId)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"select ID,Title from tblpartyarticle where Module='BranchHomeArticle' and Category='FinancePublic'
and BranchID ='{0}' and `Status`=4
order by CreateTime desc limit 0,5", branchId);

            return MySqlHelper.ExecuteDataTable(sb.ToString());
        }

        /// <summary>
        /// 获取党务公开
        /// </summary>
        /// <param name="branchId"></param>
        /// <returns></returns>
        public static DataTable GetPartyPublicData(string branchId)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"select ID,Title from tblpartyarticle where Module='BranchHomeArticle' and Category='PartyPublic'
and BranchID ='{0}' and `Status`=4
order by CreateTime desc limit 0,5", branchId);

            return MySqlHelper.ExecuteDataTable(sb.ToString());
        }
        #endregion

        #region 书记讲话
        public static DataSet GetPartySecretarySpeechData(int pageIndex, int pageSize, string title, string author, string branchId)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = "SecretarySpeech"
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  a.ID,a.Title,SecretaryName,UserName,a.CreateTime,BranchName,
if(a.Status=4,'是','否') as StatusName
from tblpartyarticle a
left join tblpartyorgmember m on a.CreateBy=m.LoginID
left join tblpartyorgbranch b on b.BranchID=a.BranchID
left join tblpartysecretary s on s.ID=Author
where Module=?Module");

            if (!string.IsNullOrEmpty(title))
            {
                sb.Append(" and a.Title like @Title ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Title",
                        Value = string.Format("%{0}%", title)
                    });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and a.BranchID in (select ChildBranchID from tblpartyorgbranchrelation brr where brr.BranchID=?BranchID) ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "BranchID",
                        Value = string.Format("{0}", branchId)
                    });
            }
            if (!string.IsNullOrEmpty(author))
            {
                sb.Append(" and Author=@Author ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Author",
                        Value = string.Format("{0}", author)
                    });
            }

            sb.Append(" order by Status desc, Flags desc, CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }
        #endregion

        #region 驻点领导
        public static DataSet GetGrassrootsData(int pageIndex, int pageSize, string category, string searchKey, string status)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = "Grassroots"
                },
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Category",
                    Value = category
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  ID,Title,CreateTime,if(Status=4,'是','否') as StatusName,if(Flags=0,'否','是') as IsUp
from tblpartyarticle
where Module=?Module and Category=?Category");

            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and Title like ?Title ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Title",
                        Value = string.Format("%{0}%", searchKey)
                    });
            }
            if (!string.IsNullOrEmpty(status))
            {
                sb.Append(" and Status=?Status");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Status",
                        Value = string.Format("{0}", status)
                    });
            }

            sb.Append(" order by Status desc, Flags desc, CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataTable GetBranchPortalWorkPublicData(string branchId)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"select ID,Title from tblpartyarticle where Module='WorkPublic' 
and BranchID in (select childbranchid from tblpartyorgbranchrelation where BranchID='{0}') and `Status`=4
order by CreateTime desc", branchId);

            return MySqlHelper.ExecuteDataTable(sb.ToString());
        }
        #endregion

        #region 群众路线
        #region 上级精神
        public static DataSet GetMassLineSuperiorSpirit(int pageIndex, int pageSize, string title, string module, string category)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = module
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };
            StringBuilder sbsql = new StringBuilder();
            sbsql.Append("SELECT SQL_CALC_FOUND_ROWS  '' as SolidTop,'' as IsTop, ID,Title,Flags,Status,CreateTime,if(Status=4,'是','否') as StatusName FROM tblpartyarticle where Module=?Module");
            if (!string.IsNullOrEmpty(title))
            {
                sbsql.Append(" and Title like ?Title ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Title",
                        Value = string.Format("%{0}%", title)
                    });
            }

            sbsql.Append(" order by Status desc, Flags desc, CreateTime desc ");
            if (pageIndex > -1)
            {
                sbsql.AppendFormat(" LIMIT {0} ,{1}; SELECT FOUND_ROWS(); ", pageIndex, pageSize);
            }

            DataSet ds = MySqlHelper.ExecuteDataSet(sbsql.ToString(), parameters.ToArray());

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                switch (dr["Flags"].ToString())
                {
                    /// 0、无；1、置顶；2、固顶；3、置顶+固顶
                    case "0": dr["SolidTop"] = "否"; dr["IsTop"] = "否"; break;
                    case "1": dr["SolidTop"] = "否"; dr["IsTop"] = "是"; break;
                    case "2": dr["SolidTop"] = "是"; dr["IsTop"] = "否"; break;
                    case "3": dr["SolidTop"] = "是"; dr["IsTop"] = "是"; break;
                }
            }
            DataTable dt = ds.Tables[0];
            return ds;
        }
        #endregion

        #region 活动简介
        /// <summary>
        /// 获取活动简介
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="module"></param>
        /// <param name="category"></param>
        /// <param name="title"></param>
        /// <param name=""></param>
        /// <returns></returns>
        public static DataSet GetActivityBulletin(int pageIndex, int pageSize, string title, string CreateBy)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = "MassLine"
                },
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Category",
                    Value = "ActivityBulletin"
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };
            StringBuilder sbsql = new StringBuilder();
            sbsql.Append(@"SELECT SQL_CALC_FOUND_ROWS  Data.*,member.UserName,branch.BranchName,'已发布' as StatusEx FROM tblpartyarticle Data 
                           LEFT JOIN tblpartyorgmember member on Data.CreateBy=member.LoginID
                           LEFT JOIN tblpartyorgbranch branch ON Data.BranchID=branch.BranchID where Module=?Module and Category=?Category ");
            if (!string.IsNullOrEmpty(title))
            {
                sbsql.Append(" and Title like @Title ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Title",
                        Value = string.Format("%{0}%", title)
                    });
            }
            if (!string.IsNullOrEmpty(CreateBy))
            {
                sbsql.Append(" and member.UserName like @CreateBy ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "CreateBy",
                        Value = string.Format("%{0}%", CreateBy)
                    });
            }
            sbsql.Append(" order by Data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            DataSet ds = MySqlHelper.ExecuteDataSet(sbsql.ToString(), parameters.ToArray());
            return ds;
        }
        #endregion

        #region 获取民情日志&学习心得
        /// <summary>
        /// 获取民情日志&学习心得
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="title"></param>
        /// <param name="branchID"></param>
        /// <param name="CreateBy"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public static DataSet GetMyDraft(int pageIndex, int pageSize, string title, string branchID, string CreateBy, string category)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Module",
                    Value = "MassLine"
                },
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "Category",
                    Value = category
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };
            StringBuilder sbsql = new StringBuilder();
            sbsql.Append(@"SELECT SQL_CALC_FOUND_ROWS  Data.*,member.UserName,branch.BranchName,if(Data.Status=4,'已发布','未发布') as StatusName FROM tblpartyarticle Data 
                           LEFT JOIN tblpartyorgmember member on Data.CreateBy=member.LoginID
                           LEFT JOIN tblpartyorgbranch branch ON Data.BranchID=branch.BranchID where Module=?Module and Category=?Category ");
            if (!string.IsNullOrEmpty(title))
            {
                sbsql.Append(" and Title like @Title ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Title",
                        Value = string.Format("%{0}%", title)
                    });
            }
            if (!string.IsNullOrEmpty(CreateBy))
            {
                sbsql.Append(" and member.UserName like @CreateBy ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "CreateBy",
                        Value = string.Format("%{0}%", CreateBy)
                    });
            }
            if (!string.IsNullOrEmpty(branchID))
            {
                sbsql.Append(" and Data.BranchID = @BranchID ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "BranchID",
                        Value = branchID
                    });
            }
            sbsql.Append(" order by Data.CreateTime desc,Data.Status desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            DataSet ds = MySqlHelper.ExecuteDataSet(sbsql.ToString(), parameters.ToArray());
            return ds;
        }
        #endregion
        #endregion

        #region 站内搜索
        public static DataSet GetWebsiteSearchResult(int pageSize, int startIndex, string searchtext)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>() {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "Serchtext",Value = string.Format("%{0}%", searchtext)}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(" select * from tblpartyarticle where Title like @Serchtext order by  Flags desc, CreateTime desc ");
            if (startIndex > -1)
            {
                sb.AppendFormat(@" limit {0},{1};", startIndex, pageSize);
            }
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static string GetWebsiteSearchResultCount(string branchId, string searchtext)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "Serchtext",Value = string.Format("%{0}%", searchtext)}
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select count(*) as count from tblpartyarticle where Title like @Serchtext");
            return MySqlHelper.ExecuteScalar(sb.ToString(), parameters.ToArray()).ToString();
        }
        #endregion
    }
}
